﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Net;
using System.Collections.Generic;
using ExamSharedClasses.Commands;
using ExamSharedClasses;
using System.Runtime.CompilerServices;

namespace ExamServer {
    /// <summary>
    /// (k) Diese Klasse soll die Schnittstelle zwischen Klassen und Datenbank
    /// darstellen. Die Datenbankverbindung wird innerhalb der Klasse gehalten und bei
    /// Methodenaufrufen automatisch geöffnet und auch wieder geschlossen...
    /// </summary>
    public class DatabaseConnection {
        /// <summary>
        /// hält die Verbindungsinformationen
        /// </summary>
        public MySqlConnectionStringBuilder MySqlConnectionStringBuilder { get; set; }
        public MySqlConnection MySqlConnection { get; set; }
        /// <summary>
        /// Bei gesetztem false werden keine Hinweise oder Fehler ausgegeben.
        /// </summary>
        public bool Verbose { get; set; }

        public DatabaseConnection() {
            MySqlConnectionStringBuilder = new MySqlConnectionStringBuilder();
            MySqlConnectionStringBuilder.Server = Connection.DATABASE_SERVER_IP;
            MySqlConnectionStringBuilder.UserID = Connection.DATABASE_USER;
            MySqlConnectionStringBuilder.Password = Connection.DATABASE_PASSWORD;
            MySqlConnectionStringBuilder.Database = Connection.DATABASE_DATABASE;
            MySqlConnection = new MySqlConnection( MySqlConnectionStringBuilder.ToString() );

            EstablishAndTestConnection();
        }
        public DatabaseConnection( string server, string user, string password, string database ) {
            MySqlConnectionStringBuilder = new MySqlConnectionStringBuilder();
            MySqlConnectionStringBuilder.Server = server;
            MySqlConnectionStringBuilder.UserID = user;
            MySqlConnectionStringBuilder.Password = password;
            MySqlConnectionStringBuilder.Database = database;
            MySqlConnection = new MySqlConnection( MySqlConnectionStringBuilder.ToString() );

            EstablishAndTestConnection();
        }
        public DatabaseConnection( MySqlConnectionStringBuilder mySqlConnectionStringBuilder ) {
            this.MySqlConnectionStringBuilder = mySqlConnectionStringBuilder;
            MySqlConnection = new MySqlConnection( MySqlConnectionStringBuilder.ToString() );

            EstablishAndTestConnection();
        }
        public DatabaseConnection( MySqlConnection mySqlConnection ) {
            MySqlConnectionStringBuilder = new MySqlConnectionStringBuilder();
            this.MySqlConnection = mySqlConnection;

            EstablishAndTestConnection();
        }
        public bool EstablishAndTestConnection() {
            Verbose = true;

            City testCity;
            MySqlConnectionStringBuilder.Server = EvaluateBestIpdAdressFromPossiblyGivenHostName( Connection.DATABASE_SERVER_IP );
            MySqlConnection = new MySqlConnection( MySqlConnectionStringBuilder.ToString() );
            if ( Verbose )
                Display( "Datenbanktest mit IP: " + MySqlConnectionStringBuilder.Server );
            testCity = GetCity( 1 );

            if ( testCity == null ) {
                MySqlConnectionStringBuilder.Server = EvaluateBestIpdAdressFromPossiblyGivenHostName( Connection.DATABASE_SERVER_IP_ALTERNATIVE );
                MySqlConnection = new MySqlConnection( MySqlConnectionStringBuilder.ToString() );
                if ( Verbose )
                    Display( "Datenbanktest mit IP: " + MySqlConnectionStringBuilder.Server );
                testCity = GetCity( 1 );
            }

            Verbose = true;

            if ( testCity != null ) {
                if ( Verbose )
                    Display( "Erfolgreicher Datenbankverbindungsaufbau mit " + MySqlConnectionStringBuilder.Server );
                return true;

            } else {
                if ( Verbose )
                    Display( "Fehler! Keine Datenbankverbindung möglich!" );
                return false;
            }
        }
        public string EvaluateBestIpdAdressFromPossiblyGivenHostName( string ipAddressOrHostName ) {

            try {   // es handelt sich um eine Ip-Adresse; diese einfach zurückgeben
                return ( IPAddress.Parse( ipAddressOrHostName ) ).ToString();
            } catch ( Exception /*ex*/) {/*Display(ex)*/;}
            if ( ipAddressOrHostName.Equals( "" ) )
                ipAddressOrHostName = "kistje";
            // ab hier ist es ein hostname...
            try {
                IPAddress[] remoteAddresses = Dns.GetHostAddresses( ipAddressOrHostName );
                foreach ( IPAddress ipAdress in remoteAddresses )
                    if ( ipAdress.ToString().StartsWith( "7." ) )
                        MySqlConnectionStringBuilder.Server = ipAdress.ToString();
            } catch ( Exception ex ) {
                Display( "Fehler beim Verbindungstest", ex );
                MySqlConnectionStringBuilder.Server = "7.107.72.163"; // für alle fälle...
            }
            return "";

        }
        public void CloseConnection() {
            MySqlConnection.Close();
        }

        // Allgemeine Datenbankstatements //
        /// <summary>
        /// (k) Dieser Methode kann ein übliches MySQL-SELECT-Statement übergeben werden, welches auf
        /// die voreingestellte Datenbank angewendet wird. Das Ergebnis wird in ein DataSet-Objekt
        /// gefüllt und zurückgegeben.
        /// </summary>
        /// <param name="statement">String der Statement enthält</param>
        /// <returns>DataSet mit SELECT-Ergebnis</returns>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public DataSet  GetDataSetByUsingSELECTStatement( string statement ) {
            try {
                DataSet dataSet = new DataSet();
                if ( Verbose )
                    Display( "DatabaseConnection: Abfrage; \n\t" + statement );

                MySqlCommand mySqlCommand = MySqlConnection.CreateCommand();
                mySqlCommand.CommandText = statement;
                MySqlDataAdapter da = new MySqlDataAdapter( mySqlCommand );
                dataSet = new DataSet();
                DataTable dataTable = new DataTable();
                MySqlConnection.Open();
                da.Fill( dataSet );
                MySqlConnection.Close();

                return dataSet;
            } catch ( Exception ex ) {
                MySqlConnection.Close();
                if ( Verbose )
                    Display( "DatabaseConnection: Fehler bei der SELECT-Abfrage!", ex );
                return null;
            }
        }
        /// <summary>
        /// (k) Dieser Methode kann ein übliches MySQL-INSERT- oder UPDATE-Statement übergeben werden,
        /// welches auf die voreingestellte Datenbank angewendet wird. Das Ergebnis wird in ein
        /// die "affected rows" als int zurückgibt
        /// gefüllt und zurückgegeben.
        /// </summary>
        /// <param name="statement">String der Statement enthält</param>
        /// <returns>(int)Menge der geänderten TDatenbankzeilen</returns>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int SetEntriesByUsingINSERTOrUPDATEStatement( string statement ) {
            try {
                if ( Verbose )
                    Display( "DatabaseConnection: Abfrage; \n\t" + statement );
                MySqlCommand mySqlCommand = MySqlConnection.CreateCommand();
                mySqlCommand.CommandText = statement;
                MySqlConnection.Open();
                int rowsAffected = mySqlCommand.ExecuteNonQuery();
                MySqlConnection.Close();
                if ( Verbose )
                    Display( rowsAffected + " affected" );
                return rowsAffected;
            } catch ( Exception ex ) {
                MySqlConnection.Close();
                if ( Verbose )
                    Display( "DatabaseConnection: Fehler bei der INSERT-Abfrage!", ex );
                return -1;
            }
        }
        /// <summary>
        /// Diese Methode holt sich die höchste ID der angegebenen Tabellenspalte
        /// aus der angegebenen Tabelle
        /// </summary>
        /// <param name="idColumnName">Tabellenspaltenname</param>
        /// <param name="tableName">Tabellenname</param>
        /// <returns>höchste ("neuste") ID</returns>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int GetMax( string idColumnName, string tableName ) {
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement( "SELECT MAX(" + idColumnName + ") FROM " + tableName + ";" ).Tables[0];
                return ( ( int )dataTable.Rows[0][0] );
            } catch ( Exception ex ) {
                Display( ex );
                return -1;
            }
        }


        // User, Userdetails, usergroup, Address, City, Country //
        /// <summary>
        /// Die Grundlegenden Informationen über einen User können mit dieser Methode aus
        /// der Datenbank abgerufen werden.
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public User GetUser( int userId ) {
            User user = new User();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            User.USER_ID + ", " +
                            User.USER_GROUP_ID + ", " +
                            User.USER_EMAIL +
                        " FROM " +
                            User.TABLENAME +
                        " WHERE " +
                            User.USER_ID + "=" + userId ) // Methode wird string übergeben, dann DataSet zurückgegeben
                        .Tables[0]; // davon den 0. Table der Variable dataTable zuweisen...

                user.UserId = ( int )dataTable.Rows[0][0];
                user.UserGroup = GetUserGroup( ( int )dataTable.Rows[0][1] );
                user.UserEmail = dataTable.Rows[0][2].ToString();

                return user;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
        /// <summary>
        /// holt detaillierte Informationen über einen User aus der Datenbank
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public UserDetails GetUserDetails( int userId ) {
            UserDetails userDetails = new UserDetails();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            UserDetails.USER_ID + ", " +
                            UserDetails.USER_GROUP_ID + ", " +
                            UserDetails.USER_NAME + ", " +
                            UserDetails.USER_SURNAME + ", " +
                            UserDetails.USER_EMAIL + ", " +
                            UserDetails.USER_BIRTHDAY + ", " +
                            UserDetails.USER_SEX + " " +
                        " FROM " +
                            UserDetails.TABLENAME +
                        " WHERE " +
                            UserDetails.USER_ID + "=" + userId ) // Methode wird string übergeben, dann DataSet zurückgegeben
                        .Tables[0]; // davon den 0. Table der Variable dataTable zuweisen...

                userDetails.UserId = ( int )dataTable.Rows[0][0];
                userDetails.UserGroup = GetUserGroup( ( int )dataTable.Rows[0][1] );
                userDetails.UserName = dataTable.Rows[0][2].ToString();
                userDetails.UserSurName = dataTable.Rows[0][3].ToString();
                userDetails.UserEmail = dataTable.Rows[0][4].ToString();
                userDetails.UserBirthDate = ( DateTime )dataTable.Rows[0][5];
                userDetails.UserSex = dataTable.Rows[0][6].Equals( "0" ) ? false : true;

                return userDetails;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
        public UserDetails GetUserDetails( string userEmail ) {
            UserDetails userDetails = new UserDetails();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        UserDetails.USER_ID + 
                    " FROM " +
                        UserDetails.TABLENAME +
                    " WHERE " +
                        UserDetails.USER_EMAIL + '=' + '"' + userEmail + '"' )
                    .Tables[0];
                return GetUserDetails( ( int )dataTable.Rows[0][0] );
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
        public UserAddress GetUserAddress( int userId ) {
            UserAddress userAddress = new UserAddress( GetUserDetails( userId) );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            UserAddress.ADDRESS_USER_ID + ", " +
                            UserAddress.ADDRESS_STREETNAME + ", " +
                            UserAddress.ADDRESS_STREETNUMBER + ", " +
                            UserAddress.ADDRESS_POSTAL_CODE + ", " +
                            UserAddress.ADDRESS_CITY_ID + ", " +
                            UserAddress.ADDRESS_COUNTRY_ID +
                        " FROM " +
                            UserAddress.TABLENAME +
                        " WHERE " +
                            UserAddress.ADDRESS_USER_ID + "=" + userId.ToString() + ";" )
                        .Tables[0];

                userAddress.UserId = ( int )dataTable.Rows[0][0];
                userAddress.AddressStreetName = ( string )dataTable.Rows[0][1];
                userAddress.AddressStreetNumber = ( string )dataTable.Rows[0][2];
                userAddress.AddressPostalCode = ( string )dataTable.Rows[0][3];

                userAddress.AddressCity = GetCity( Int32.Parse( dataTable.Rows[0][4].ToString() ) );

                userAddress.AddressCountry = GetCountry( Int32.Parse( dataTable.Rows[0][5].ToString() ) );

                return userAddress;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public UserAddress GetUserAddress( string userEmail ) {
            UserAddress userAddress = new UserAddress();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        UserDetails.USER_ID +
                    " FROM " +
                        UserDetails.TABLENAME +
                    " WHERE " +
                        UserDetails.USER_EMAIL + '=' + '"' + userEmail + '"' )
                    .Tables[0];
                return GetUserAddress( ( int )dataTable.Rows[0][0] );
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
       
        public int SetUser( User user, Password password ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + User.TABLENAME + " (" +
                        User.USER_ID + ", " +
                        User.USER_GROUP_ID + ", " +
                        User.USER_EMAIL +
                    " ) VALUES (" +
                        user.UserId + ", " +
                        user.UserGroup.GroupId + ", " +
                        '"' + user.UserEmail + '"' + ", " +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + User.TABLENAME +
                            " SET " +
                                User.USER_GROUP_ID + "=" + user.UserGroup.GroupId + ", " +
                                User.USER_EMAIL + "=" + '"' + user.UserEmail + '"' +
                            " WHERE " +
                                User.USER_ID + "=" + user.UserId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        public int SetUserDetails( UserDetails userDetail, Password password ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + UserDetails.TABLENAME + " (" +
                        UserDetails.USER_ID + ", " +
                        UserDetails.USER_GROUP_ID + ", " +
                        UserDetails.USER_NAME + ", " +
                        UserDetails.USER_SURNAME + ", " +
                        UserDetails.USER_EMAIL + ", " +
                        UserDetails.USER_BIRTHDAY + ", " +
                        UserDetails.USER_SEX +
                    " ) VALUES (" +
                        userDetail.UserId + ", " +
                        userDetail.UserGroup.GroupId + ", " +
                        '"' + userDetail.UserName + '"' + ", " +
                        '"' + userDetail.UserSurName + '"' + ", " +
                        '"' + userDetail.UserEmail + '"' + ", " +
                        '"' + userDetail.UserBirthDate + '"' + ", " +
                        ( userDetail.UserSex ? "1" : "0" ) +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + UserDetails.TABLENAME +
                            " SET " +
                                UserDetails.USER_GROUP_ID + "=" + userDetail.UserGroup.GroupId + ", " +
                                UserDetails.USER_NAME + "=" + '"' + userDetail.UserName + '"' + ", " +
                                UserDetails.USER_SURNAME + "=" + '"' + userDetail.UserSurName + '"' + ", " +
                                UserDetails.USER_EMAIL + "=" + '"' + userDetail.UserEmail + '"' + ", " +
                                UserDetails.USER_BIRTHDAY + "=" + '"' + userDetail.UserBirthDate + '"' + ", " +
                                UserDetails.USER_SEX + "=" + ( userDetail.UserSex ? "1" : "0" ) + " " +
                            " WHERE " +
                                UserDetails.USER_ID + "=" + userDetail.UserId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        /*public int SetAddress( UserAddress address ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + UserAddress.TABLENAME + " (" +
                            UserAddress.ADDRESS_USER_ID + ", " +
                            UserAddress.ADDRESS_STREETNAME + ", " +
                            UserAddress.ADDRESS_STREETNUMBER + ", " +
                            UserAddress.ADDRESS_POSTAL_CODE + ", " +
                            UserAddress.ADDRESS_CITY_ID + ", " +
                            UserAddress.ADDRESS_COUNTRY_ID +
                    " ) VALUES (" +
                            UserAddress.ADDRESS_USER_ID + ", " +
                            address.AddressStreetName + ", " +
                            '"' + address.AddressStreetNumber + '"' + ", " +
                            address.AddressPostalCode + ", " +
                            address.AddressCity.CityId + ", " +
                            address.AddressCountry.CountryId +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + UserAddress.TABLENAME +
                            " SET " +
                                UserAddress.ADDRESS_STREETNAME + "=" + address.AddressStreetName + ", " +
                                UserAddress.ADDRESS_STREETNUMBER + "=" + '"' + address.AddressStreetNumber + '"' + ", " +
                                UserAddress.ADDRESS_POSTAL_CODE + "=" + address.AddressPostalCode + ", " +
                                UserAddress.ADDRESS_CITY_ID + "=" + address.AddressCity.CityId + ", " +
                                UserAddress.ADDRESS_COUNTRY_ID + "=" + address.AddressCountry.CountryId +
                            " WHERE " +
                                UserAddress.ADDRESS_USER_ID + "=" + address.UserId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }*/
        
        public UserGroup GetUserGroup( int userGroupId ) {
            UserGroup userGroup = new UserGroup();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            UserGroup.GROUP_ID + ", " +
                            UserGroup.GROUP_NAME +
                        " FROM " +
                            UserGroup.TABLENAME +
                        " WHERE " +
                            UserGroup.GROUP_ID + "=" + userGroupId.ToString() )
                    .Tables[0];
                userGroup.GroupId = ( int )dataTable.Rows[0][0];
                userGroup.GroupName = ( string )dataTable.Rows[0][1];
                return userGroup;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }    
        public City GetCity( int cityId ) {
            City city = new City();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            City.CITY_ID + ", " +
                            City.CITY_NAME +
                        " FROM " +
                            City.TABLENAME +
                        " WHERE " +
                            City.CITY_ID + "=" + cityId.ToString() )
                    .Tables[0];
                city.CityId = ( int )dataTable.Rows[0][0];
                city.CityName = ( string )dataTable.Rows[0][1];
                return city;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
        public Country GetCountry( int countryId ) {
            Country country = new Country();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        Country.COUNTRY_ID + ", " +
                        Country.COUNTRY_NAME + ", " +
                        Country.COUNTRY_TAG +
                    " FROM " +
                        Country.TABLENAME +
                    " WHERE " +
                        Country.COUNTRY_ID + "=" + countryId.ToString() )
                    .Tables[0];
                country.CountryId = ( int )dataTable.Rows[0][0];
                country.CountryName = ( string )dataTable.Rows[0][1];
                country.CountryTag = ( string )dataTable.Rows[0][2];
                return country;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
        public int SetUserGroup( UserGroup userGroup ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + UserGroup.TABLENAME + " (" +
                        UserGroup.GROUP_ID + ", " +
                        UserGroup.GROUP_NAME +
                    " ) VALUES (" +
                        userGroup.GroupId + ", " +
                        '"' + userGroup.GroupName + '"' +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + UserGroup.TABLENAME +
                            " SET " +
                                UserGroup.GROUP_ID + "=" + '"' + userGroup.GroupId + '"' +
                            " WHERE " +
                                UserGroup.GROUP_NAME + "=" + userGroup.GroupName +
                            ";" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        
        public int SetCity( City city ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + City.TABLENAME + " (" +
                        City.CITY_ID + ", " +
                        City.CITY_NAME +
                    " ) VALUES (" +
                        city.CityId + ", " +
                        '"' + city.CityName + '"' +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + City.TABLENAME +
                            " SET " +
                                City.CITY_NAME + "=" + '"' + city.CityName + '"' +
                            " WHERE " +
                                City.CITY_ID + "=" + city.CityId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        public int SetCountry( Country country ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Country.TABLENAME + " (" +
                        Country.COUNTRY_ID + ", " +
                        Country.COUNTRY_NAME +
                        Country.COUNTRY_TAG +
                    " ) VALUES (" +
                        country.CountryId + ", " +
                        '"' + country.CountryName + '"' +
                        '"' + country.CountryTag + '"' +
                    ");" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Country.TABLENAME +
                            " SET " +
                                Country.COUNTRY_NAME + "=" + '"' + country.CountryName + '"' +
                                Country.COUNTRY_TAG + "=" + '"' + country.CountryTag + '"' +
                            " WHERE " +
                                Country.COUNTRY_ID + "=" + country.CountryId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }


        /// <summary>
        /// Das Passwort anhand der userId ermitteln.
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public Password GetPassword( int userId ) {
            Password password = new Password();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        Password.USER_ID + ", " +
                        Password.PASSWORD_HASH +
                    " FROM " +
                        Password.TABLENAME +
                    " WHERE " +
                        Password.USER_ID + "=" + '"' + userId.ToString() + '"' )
                    .Tables[0];

                password.UserId = ( int )dataTable.Rows[0][0];
                password.PasswordHash = dataTable.Rows[0][1].ToString();
                return password;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

        }
        /// <summary>
        /// Das Passwort anhand des passwordHashes ermitteln.
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public Password GetPassword( string passwordHash ) {
            Password password = new Password();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            Password.USER_ID + ", " +
                            Password.PASSWORD_HASH +
                        " FROM " +
                            Password.TABLENAME +
                        " WHERE " +
                            Password.PASSWORD_HASH + "=" + '"' + passwordHash + '"' )
                        .Tables[0];

                password.UserId = ( int )dataTable.Rows[0][0];
                password.PasswordHash = dataTable.Rows[0][1].ToString();
                return password;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        /// <summary>
        /// Ein Passwortobjekt wird in die Tabelle eingetragen, sollte es sich um
        /// eine neue Verknpfung mit einem User handeln; sonst wird eine Änderung
        /// des Passwortes vorgenommen.
        /// </summary>
        /// <param name="password"></param>
        /// <returns></returns>
        public int SetPassword( Password password ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Password.TABLENAME + " (" +
                        Password.USER_ID + ", " +
                        Password.PASSWORD_HASH +
                    " ) VALUES (" +
                        password.UserId + ", " +
                        '"' + password.PasswordHash + '"' +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Password.TABLENAME +
                            " SET " +
                                Password.PASSWORD_HASH + '=' + '"' + password.PasswordHash + '"' +
                            " WHERE " +
                                Password.USER_ID + "=" + password.UserId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }


        /// <summary>
        /// Diese Methode ermittelt Question-Objekt aus der Datenbank und spezialisiert es
        /// entsprechend des Fragentyps. Es werden dabei die drei weiteren Question-Methoden
        /// verwendet ( 
        ///     GetQuestionTypeMultiple( Question question ); 
        ///     GetQuestionTypeSingleMultiple( Question question );
        ///     GetQuestionTypeText( Question question );
        /// </summary>
        /// <param name="questionId"></param>
        /// <returns></returns>
        public Question GetQuestion( int questionId ) {
            Question question = new Question();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            Question.QUESTION_ID + ", " +
                            Question.QUESTION_TYPE + ", " +
                            Question.QUESTION_NUMBER +
                        " FROM " +
                            Question.TABLENAME +
                        " WHERE " +
                            Question.QUESTION_ID + "=" + questionId.ToString() )
                    .Tables[0];
                question.QuestionId = ( int )dataTable.Rows[0][0];
                question.QuestionTypeObject = ( string )dataTable.Rows[0][1];
                question.QuestionNumber = ( int )dataTable.Rows[0][2];

                switch ( question.QuestionTypeObject ) {
                    case ( "multiple" ):
                        return GetQuestionTypeMultiple( question );

                    case ( "single_multiple" ):
                        return GetQuestionTypeSingleMultiple( question );

                    case ( "text" ):
                        return GetQuestionTypeText( question );
                }

                return question;

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        /// <summary>
        /// Diese Methode wird von der GetQuestion( int questionId )-Methode aufgerufen, sollte
        /// das abgefragte Element eine Multiple-Choice-Frage sein. QuestionTypeObject=="multiple"
        /// </summary>
        /// <param name="question"></param>
        /// <returns></returns>
        private QuestionTypeMultiple GetQuestionTypeMultiple( Question question ) {
            QuestionTypeMultiple questionTypeMultiple = new QuestionTypeMultiple( question );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            QuestionTypeMultiple.QUESTION_NUMBER + ", " +
                            QuestionTypeMultiple.QUESTION_AUTHOR + ", " +
                            QuestionTypeMultiple.QUESTION_BODY + ", " +
                            QuestionTypeMultiple.QUESTION_ANSWER1 + ", " +
                            QuestionTypeMultiple.QUESTION_ANSWER2 + ", " +
                            QuestionTypeMultiple.QUESTION_ANSWER3 + ", " +
                            QuestionTypeMultiple.QUESTION_ANSWER4 + ", " +
                            QuestionTypeMultiple.QUESTION_RIGHT_ANSWER + ", " +
                            QuestionTypeMultiple.QUESTION_ACTIVE +
                        " FROM " +
                            QuestionTypeMultiple.TABLENAME +
                        " WHERE " +
                            QuestionTypeMultiple.QUESTION_NUMBER + "=" + question.QuestionNumber + "" )
                    .Tables[0];

                questionTypeMultiple.QuestionNumber = ( int )dataTable.Rows[0][0];
                questionTypeMultiple.QuestionAuthor = GetUser( ( int )dataTable.Rows[0][1] );
                questionTypeMultiple.QuestionBody = ( string )dataTable.Rows[0][2];
                questionTypeMultiple.QuestionAnswer1 = ( string )dataTable.Rows[0][3];
                questionTypeMultiple.QuestionAnswer2 = ( string )dataTable.Rows[0][4];
                questionTypeMultiple.QuestionAnswer3 = ( string )dataTable.Rows[0][5];
                questionTypeMultiple.QuestionAnswer4 = ( string )dataTable.Rows[0][6];
                questionTypeMultiple.QuestionRightAnswer = ( int )dataTable.Rows[0][7];
                questionTypeMultiple.QuestionActive = dataTable.Rows[0][8].Equals( "0" ) ? false : true;
                return questionTypeMultiple;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        /// <summary>
        /// Diese Methode wird von der GetQuestion( int questionId )-Methode aufgerufen, sollte
        /// das abgefragte Element eine Multiple-Choice-Frage mit nur einer richtigen Antwort
        /// sein. QuestionTypeObject=="single_multiple"
        /// </summary>
        /// <param name="question"></param>
        /// <returns></returns>
        private QuestionTypeSingleMultiple GetQuestionTypeSingleMultiple( Question question ) {
            QuestionTypeSingleMultiple questionTypeSingleMultiple = new QuestionTypeSingleMultiple( question );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            QuestionTypeSingleMultiple.QUESTION_NUMBER + ", " +
                            QuestionTypeSingleMultiple.QUESTION_AUTHOR + ", " +
                            QuestionTypeSingleMultiple.QUESTION_BODY + ", " +
                            QuestionTypeSingleMultiple.QUESTION_ANSWER1 + ", " +
                            QuestionTypeSingleMultiple.QUESTION_ANSWER2 + ", " +
                            QuestionTypeSingleMultiple.QUESTION_ANSWER3 + ", " +
                            QuestionTypeSingleMultiple.QUESTION_ANSWER4 + ", " +
                            QuestionTypeSingleMultiple.QUESTION_RIGHT_ANSWER + ", " +
                            QuestionTypeSingleMultiple.QUESTION_ACTIVE +
                        " FROM " +
                            QuestionTypeSingleMultiple.TABLENAME +
                        " WHERE " +
                            QuestionTypeSingleMultiple.QUESTION_NUMBER + "=" + question.QuestionNumber + "" )
                    .Tables[0];

                questionTypeSingleMultiple.QuestionNumber = ( int )dataTable.Rows[0][0];
                questionTypeSingleMultiple.QuestionAuthor = GetUser( ( int )dataTable.Rows[0][1] );
                questionTypeSingleMultiple.QuestionBody = ( string )dataTable.Rows[0][2];
                questionTypeSingleMultiple.QuestionAnswer1 = ( string )dataTable.Rows[0][3];
                questionTypeSingleMultiple.QuestionAnswer2 = ( string )dataTable.Rows[0][4];
                questionTypeSingleMultiple.QuestionAnswer3 = ( string )dataTable.Rows[0][5];
                questionTypeSingleMultiple.QuestionAnswer4 = ( string )dataTable.Rows[0][6];
                questionTypeSingleMultiple.QuestionRightAnswer = ( int )dataTable.Rows[0][7];
                questionTypeSingleMultiple.QuestionActive = dataTable.Rows[0][8].Equals( "0" ) ? false : true;
                return questionTypeSingleMultiple;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        /// <summary>
        /// Diese Methode wird von der GetQuestion( int questionId )-Methode aufgerufen, sollte
        /// das abgefragte Element eine Frage mit Textantwort sein. QuestionTypeObject=="text"
        /// </summary>
        /// <param name="question"></param>
        /// <returns></returns>
        private QuestionTypeText GetQuestionTypeText( Question question ) {
            QuestionTypeText questionTypeText = new QuestionTypeText( question );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            QuestionTypeText.QUESTION_NUMBER + ", " +
                            QuestionTypeText.QUESTION_AUTHOR + ", " +
                            QuestionTypeText.QUESTION_BODY + ", " +
                            QuestionTypeText.QUESTION_RIGHT_ANSWER + ", " +
                            QuestionTypeText.QUESTION_ACTIVE +
                        " FROM " +
                            QuestionTypeText.TABLENAME +
                        " WHERE " +
                            QuestionTypeText.QUESTION_NUMBER + "=" + question.QuestionNumber + "" )
                    .Tables[0];

                questionTypeText.QuestionNumber = ( int )dataTable.Rows[0][0];
                questionTypeText.QuestionAuthor = GetUser( ( int )dataTable.Rows[0][1] );
                questionTypeText.QuestionBody = ( string )dataTable.Rows[0][2];
                questionTypeText.QuestionRightAnswer = ( string )dataTable.Rows[0][3];
                questionTypeText.QuestionActive = dataTable.Rows[0][4].Equals( "0" ) ? false : true;

                return questionTypeText;

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }


        public int SetQuestion( Question question ) {
            try {
                if ( question is QuestionTypeMultiple ) {
                    SetQuestionTypeMultiple( ( QuestionTypeMultiple )question );
                    question.QuestionNumber = GetNewAnswerTypeMultipleId();
                } else if ( question is QuestionTypeSingleMultiple ) {
                    SetQuestionTypeSingleMultiple( ( QuestionTypeSingleMultiple )question );
                    question.QuestionNumber = GetNewAnswerTypeSingleMultipleId();
                }

                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Question.TABLENAME + " (" +
                        Question.QUESTION_ID + ", " +
                        Question.QUESTION_TYPE + ", " +
                        Question.QUESTION_NUMBER +
                    " ) VALUES (" +
                        question.QuestionId + ", " +
                        '"' + question.QuestionTypeObject + '"' + ", " +
                        question.QuestionNumber +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Question.TABLENAME +
                            " SET " +
                                Question.QUESTION_TYPE + "=" + '"' + question.QuestionTypeObject + '"' + ", " +
                                Question.QUESTION_NUMBER + "=" + question.QuestionNumber +
                            " WHERE " +
                                Question.QUESTION_ID + "=" + question.QuestionId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        public int SetQuestionTypeMultiple( QuestionTypeMultiple questionTypeMultiple ) {
            try {
                questionTypeMultiple.QuestionNumber = GetNewAnswerTypeMultipleId();
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + QuestionTypeMultiple.TABLENAME + " (" +
                        QuestionTypeMultiple.QUESTION_AUTHOR + ", " +
                        QuestionTypeMultiple.QUESTION_BODY + ", " +
                        QuestionTypeMultiple.QUESTION_ANSWER1 + ", " +
                        QuestionTypeMultiple.QUESTION_ANSWER2 + ", " +
                        QuestionTypeMultiple.QUESTION_ANSWER3 + ", " +
                        QuestionTypeMultiple.QUESTION_ANSWER4 + ", " +
                        QuestionTypeMultiple.QUESTION_RIGHT_ANSWER + ", " +
                        QuestionTypeMultiple.QUESTION_ACTIVE +
                    " ) VALUES (" +
                        questionTypeMultiple.QuestionAuthor.UserId + ", " +
                        '"' + questionTypeMultiple.QuestionBody + '"' + ", " +
                        '"' + questionTypeMultiple.QuestionAnswer1 + '"' + ", " +
                        '"' + questionTypeMultiple.QuestionAnswer2 + '"' + ", " +
                        '"' + questionTypeMultiple.QuestionAnswer3 + '"' + ", " +
                        '"' + questionTypeMultiple.QuestionAnswer4 + '"' + ", " +
                        questionTypeMultiple.QuestionRightAnswer + ", " +
                        ( questionTypeMultiple.QuestionActive ? "1" : "0" ) +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + QuestionTypeMultiple.TABLENAME +
                            " SET " +
                                QuestionTypeMultiple.QUESTION_AUTHOR + "=" + questionTypeMultiple.QuestionAuthor.UserId + ", " +
                                QuestionTypeMultiple.QUESTION_BODY + "=" + '"' + questionTypeMultiple.QuestionBody + '"' + ", " +
                                QuestionTypeMultiple.QUESTION_ANSWER1 + "=" + '"' + questionTypeMultiple.QuestionAnswer1 + '"' + ", " +
                                QuestionTypeMultiple.QUESTION_ANSWER2 + "=" + '"' + questionTypeMultiple.QuestionAnswer2 + '"' + ", " +
                                QuestionTypeMultiple.QUESTION_ANSWER3 + "=" + '"' + questionTypeMultiple.QuestionAnswer3 + '"' + ", " +
                                QuestionTypeMultiple.QUESTION_ANSWER4 + "=" + '"' + questionTypeMultiple.QuestionAnswer4 + '"' + ", " +
                                QuestionTypeMultiple.QUESTION_RIGHT_ANSWER + "=" + questionTypeMultiple.QuestionRightAnswer + ", " +
                                QuestionTypeMultiple.QUESTION_ACTIVE + "=" + ( questionTypeMultiple.QuestionActive ? "1" : "0" ) +
                            " WHERE " +
                                QuestionTypeMultiple.QUESTION_ID + "=" + questionTypeMultiple.QuestionId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        public int SetQuestionTypeSingleMultiple( QuestionTypeSingleMultiple questionTypeSingleMultiple ) {
            try {
                questionTypeSingleMultiple.QuestionNumber = GetNewAnswerTypeSingleMultipleId();
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + QuestionTypeSingleMultiple.TABLENAME + " (" +
                    //QuestionTypeSingleMultiple.QUESTION_ID + ", " +
                        QuestionTypeSingleMultiple.QUESTION_AUTHOR + ", " +
                        QuestionTypeSingleMultiple.QUESTION_BODY + ", " +
                        QuestionTypeSingleMultiple.QUESTION_ANSWER1 + ", " +
                        QuestionTypeSingleMultiple.QUESTION_ANSWER2 + ", " +
                        QuestionTypeSingleMultiple.QUESTION_ANSWER3 + ", " +
                        QuestionTypeSingleMultiple.QUESTION_ANSWER4 + ", " +
                        QuestionTypeSingleMultiple.QUESTION_RIGHT_ANSWER + ", " +
                        QuestionTypeSingleMultiple.QUESTION_ACTIVE +
                    " ) VALUES (" +
                    //questionTypeMultiple.QuestionId + ", " +
                        questionTypeSingleMultiple.QuestionAuthor.UserId + ", " +
                        '"' + questionTypeSingleMultiple.QuestionBody + '"' + ", " +
                        '"' + questionTypeSingleMultiple.QuestionAnswer1 + '"' + ", " +
                        '"' + questionTypeSingleMultiple.QuestionAnswer2 + '"' + ", " +
                        '"' + questionTypeSingleMultiple.QuestionAnswer3 + '"' + ", " +
                        '"' + questionTypeSingleMultiple.QuestionAnswer4 + '"' + ", " +
                        questionTypeSingleMultiple.QuestionRightAnswer + ", " +
                        ( questionTypeSingleMultiple.QuestionActive ? "1" : "0" ) +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + QuestionTypeSingleMultiple.TABLENAME +
                            " SET " +
                                QuestionTypeSingleMultiple.QUESTION_AUTHOR + "=" + questionTypeSingleMultiple.QuestionAuthor.UserId + ", " +
                                QuestionTypeSingleMultiple.QUESTION_BODY + "=" + '"' + questionTypeSingleMultiple.QuestionBody + '"' + ", " +
                                QuestionTypeSingleMultiple.QUESTION_ANSWER1 + "=" + '"' + questionTypeSingleMultiple.QuestionAnswer1 + '"' + ", " +
                                QuestionTypeSingleMultiple.QUESTION_ANSWER2 + "=" + '"' + questionTypeSingleMultiple.QuestionAnswer2 + '"' + ", " +
                                QuestionTypeSingleMultiple.QUESTION_ANSWER3 + "=" + '"' + questionTypeSingleMultiple.QuestionAnswer3 + '"' + ", " +
                                QuestionTypeSingleMultiple.QUESTION_ANSWER4 + "=" + '"' + questionTypeSingleMultiple.QuestionAnswer4 + '"' + ", " +
                                QuestionTypeSingleMultiple.QUESTION_RIGHT_ANSWER + "=" + questionTypeSingleMultiple.QuestionRightAnswer + ", " +
                                QuestionTypeSingleMultiple.QUESTION_ACTIVE + "=" + ( questionTypeSingleMultiple.QuestionActive ? "1" : "0" ) +
                            " WHERE " +
                                QuestionTypeSingleMultiple.QUESTION_ID + "=" + questionTypeSingleMultiple.QuestionId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        public int SetQuestionTypeText( QuestionTypeText questionTypeText ) {
            try {
                questionTypeText.QuestionNumber = GetNewAnswerTypeSingleMultipleId();
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + QuestionTypeText.TABLENAME + " (" +
                        QuestionTypeText.QUESTION_AUTHOR + ", " +
                        QuestionTypeText.QUESTION_BODY + ", " +
                        QuestionTypeText.QUESTION_RIGHT_ANSWER + ", " +
                        QuestionTypeText.QUESTION_ACTIVE +
                    " ) VALUES (" +
                        questionTypeText.QuestionAuthor.UserId + ", " +
                        '"' + questionTypeText.QuestionBody + '"' + ", " +
                        questionTypeText.QuestionRightAnswer + ", " +
                        ( questionTypeText.QuestionActive ? "1" : "0" ) +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + QuestionTypeText.TABLENAME +
                            " SET " +
                                QuestionTypeText.QUESTION_AUTHOR + "=" + questionTypeText.QuestionAuthor.UserId + ", " +
                                QuestionTypeText.QUESTION_BODY + "=" + '"' + questionTypeText.QuestionBody + '"' + ", " +
                                QuestionTypeText.QUESTION_RIGHT_ANSWER + "=" + questionTypeText.QuestionRightAnswer + ", " +
                                QuestionTypeText.QUESTION_ACTIVE + "=" + ( questionTypeText.QuestionActive ? "1" : "0" ) +
                            " WHERE " +
                                QuestionTypeText.QUESTION_ID + "=" + questionTypeText.QuestionId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }


        // answer, answertypemultiple, answertypesinglemultiple
        public Answer GetAnswer( int answerExamId, Question answerQuestion, int answerUserId ) {

            Answer answer = new Answer();

            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                            "SELECT " +
                                Answer.ANSWER_EXAM_ID + ", " +
                                Answer.ANSWER_QUESTION_ID + ", " +
                                Answer.ANSWER_USER_ID + ", " +
                                Answer.ANSWER_NUMBER + ", " +
                                Answer.ANSWER_RATING +
                            " FROM " +
                                Answer.TABLENAME +
                            " WHERE " +
                                Answer.ANSWER_EXAM_ID + "=" + answerExamId.ToString() + " AND " +
                                Answer.ANSWER_QUESTION_ID + "=" + answerQuestion.QuestionId.ToString() + " AND " +
                                Answer.ANSWER_USER_ID + "=" + answerUserId.ToString() + ";" )
                            .Tables[0];

                answer.AnswerExamId = ( int )dataTable.Rows[0][0];
                answer.AnswerQuestion = answerQuestion;
                answer.AnswerUserId = ( int )dataTable.Rows[0][2];
                answer.AnswerNumber = ( int )dataTable.Rows[0][3];
                answer.AnswerRating = ( int )dataTable.Rows[0][4];

                switch ( answer.AnswerQuestion.QuestionTypeObject ) {
                    case ( "multiple" ):
                        return GetAnswerTypeMultiple( answer );
                    case ( "single_multiple" ):
                        return GetAnswerTypeSingleMultiple( answer );
                    case ( "text" ):
                        return GetAnswerTypeText( answer );
                    default:
                        break;
                }

                return answer;

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public AnswerTypeMultiple GetAnswerTypeMultiple( Answer answer ) {
            AnswerTypeMultiple answerTypeMultiple = new AnswerTypeMultiple( answer );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            AnswerTypeMultiple.ANSWER_USER_ANSWER +
                        " FROM " +
                            AnswerTypeMultiple.TABLENAME +
                        " WHERE " +
                            AnswerTypeMultiple.ANSWER_NUMBER + "=" + answer.AnswerNumber + ";" )
                    .Tables[0];

                answerTypeMultiple.AnswerUserAnswer = ( int )dataTable.Rows[0][0];
                return answerTypeMultiple;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public AnswerTypeSingleMultiple GetAnswerTypeSingleMultiple( Answer answer ) {
            AnswerTypeSingleMultiple answerTypeMultiple = new AnswerTypeSingleMultiple( answer );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            AnswerTypeSingleMultiple.ANSWER_USER_ANSWER +
                        " FROM " +
                            AnswerTypeSingleMultiple.TABLENAME +
                        " WHERE " +
                            AnswerTypeSingleMultiple.ANSWER_NUMBER + "=" + answerTypeMultiple.AnswerNumber + ";" )
                    .Tables[0];

                answerTypeMultiple.AnswerUserAnswer = ( int )dataTable.Rows[0][0];
                return answerTypeMultiple;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public AnswerTypeText GetAnswerTypeText( Answer answer ) {
            AnswerTypeText answerTypeText = new AnswerTypeText( answer );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            AnswerTypeText.ANSWER_USER_ANSWER +
                        " FROM " +
                            AnswerTypeText.TABLENAME +
                        " WHERE " +
                            AnswerTypeText.ANSWER_NUMBER + "=" + answerTypeText.AnswerNumber + ";" )
                    .Tables[0];

                answerTypeText.AnswerUserAnswer = ( string )dataTable.Rows[0][0];
                return answerTypeText;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }

        public int SetAnswer( Answer answer ) {
            try {

                // Die speziellere AnswerObjektVersion zuerst in die spezielle tabelle tragen,
                // da die AnswerNumber noch nicht bekannt ist, und nachträglich ermittelt werden
                // muß, sie gehört dann ja auch in die allgemeine answer-tabelle...
                if ( answer is AnswerTypeMultiple ) {
                    SetAnswerTypeMultiple( ( AnswerTypeMultiple )answer );
                    answer.AnswerNumber = GetNewAnswerTypeMultipleId();

                } else if ( answer is AnswerTypeSingleMultiple ) {
                    SetAnswerTypeSingleMultiple( ( AnswerTypeSingleMultiple )answer );
                    answer.AnswerNumber = GetNewAnswerTypeSingleMultipleId();

                } else if ( answer is AnswerTypeText ) {
                    SetAnswerTypeText( ( AnswerTypeText )answer );
                    answer.AnswerNumber = GetNewAnswerTypeTextId();

                }

                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Answer.TABLENAME + " (" +
                            Answer.ANSWER_EXAM_ID + ", " +
                            Answer.ANSWER_QUESTION_ID + ", " +
                            Answer.ANSWER_USER_ID + ", " +
                            Answer.ANSWER_NUMBER + ", " +
                            Answer.ANSWER_RATING +
                    " ) VALUES (" +
                        answer.AnswerExamId + ", " +
                        answer.AnswerQuestion.QuestionId + ", " +
                        answer.AnswerUserId + ", " +
                        answer.AnswerNumber + ", " +
                        answer.AnswerRating +
                    " );" );
                return rowsAffected;

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        private int SetAnswerTypeMultiple( AnswerTypeMultiple answerTypeMultiple ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + AnswerTypeMultiple.TABLENAME + " (" +
                        AnswerTypeMultiple.ANSWER_USER_ANSWER +
                    " ) VALUES (" +
                        answerTypeMultiple.AnswerUserAnswer +
                    " );" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        private int SetAnswerTypeSingleMultiple( AnswerTypeSingleMultiple answerTypeSingleMultiple ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + AnswerTypeSingleMultiple.TABLENAME + " (" +
                        AnswerTypeSingleMultiple.ANSWER_USER_ANSWER +
                    " ) VALUES (" +
                        answerTypeSingleMultiple.AnswerUserAnswer +
                    " );" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }
        private int SetAnswerTypeText( AnswerTypeText answerTypeText ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + AnswerTypeText.TABLENAME + " (" +
                        AnswerTypeText.ANSWER_USER_ANSWER +
                    " ) VALUES (" +
                        '"' + answerTypeText.AnswerUserAnswer + '"' +
                    " );" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }


        // module, course, class    
        public Module GetModule( int moduleId ) {
            Module module = new Module();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            Module.MODULE_ID + ", " +
                            Module.MODULE_NAME + ", " +
                            Module.MODULE_DESCRIPTION +
                        " FROM " +
                            Module.TABLENAME +
                        " WHERE " +
                            Module.MODULE_ID + "=" + moduleId.ToString() )
                    .Tables[0];
                module.ModuleId = ( int )dataTable.Rows[0][0];
                module.ModuleName = ( string )dataTable.Rows[0][1];
                module.ModuleDescription = ( string )dataTable.Rows[0][2];
                return module;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public int SetModuleObjectIntoDatabase( Module module ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Module.TABLENAME + " (" +
                        Module.MODULE_ID + ", " +
                        Module.MODULE_NAME + ", " +
                        Module.MODULE_DESCRIPTION +
                    " ) VALUES (" +
                        module.ModuleId + ", " +
                        '"' + module.ModuleName + '"' + ", " +
                        '"' + module.ModuleDescription + '"' +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Module.TABLENAME +
                            " SET " +
                                Module.MODULE_NAME + "=" + '"' + module.ModuleName + '"' + ", " +
                                Module.MODULE_DESCRIPTION + "=" + '"' + module.ModuleDescription + '"' +
                            " WHERE " +
                                Module.MODULE_ID + "=" + module.ModuleId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }

        public Course GetCourse( int courseId ) {
            Course course = new Course();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        Course.COURSE_ID + ", " +
                        Course.COURSE_NAME +
                    " FROM " +
                        Course.TABLENAME +
                    " WHERE " +
                        Course.COURSE_ID + "=" + courseId.ToString() )
                    .Tables[0];
                course.CourseId = ( int )dataTable.Rows[0][0];
                course.CourseName = ( string )dataTable.Rows[0][1];
                return course;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public int SetCourse( Course course ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Course.TABLENAME + " (" +
                        Course.COURSE_ID + ", " +
                        Course.COURSE_NAME +
                    " ) VALUES (" +
                        course.CourseId + ", " +
                        '"' + course.CourseName + '"' +
                    ");" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Course.TABLENAME +
                            " SET " +
                                Course.COURSE_NAME + "=" + '"' + course.CourseName + '"' +
                            " WHERE " +
                                Course.COURSE_ID + "=" + course.CourseId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }

        /// <summary>
        /// (k) Holt ein Klassen-Objekt aus der Datenbank...
        /// </summary>
        /// <param name="classId"></param>
        /// <returns></returns>
        public Class GetClass( int classId ) {
            Class @class = new Class();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            Class.CLASS_ID + ", " +
                            Class.COURSE_ID + ", " +
                            Class.CLASS_NAME + ", " +
                            Class.CLASS_BEGIN + ", " +
                            Class.CLASS_END +
                        " FROM " +
                            Class.TABLENAME +
                        " WHERE " +
                            Class.COURSE_ID + "=" + classId.ToString() )
                    .Tables[0];
                @class.ClassId = ( int )dataTable.Rows[0][0];
                @class.Course = GetCourse( ( int )dataTable.Rows[0][1] );
                @class.ClassName = ( string )dataTable.Rows[0][2];
                @class.ClassBegin = ( DateTime )dataTable.Rows[0][3];
                @class.ClassEnd = ( DateTime )dataTable.Rows[0][4];
                return @class;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        /// <summary>
        /// (k) Holt das speziellere ClassUser-Objekt aus der Datenbank.
        /// In der Liste sind UserDetailsObjekte enthalten;
        /// </summary>
        /// <param name="classId"></param>
        /// <returns></returns>
        public ClassWithUsers GetClassWithUsers( int classId ) {
            ClassWithUsers cwu = new ClassWithUsers( GetClass( classId ) );
            DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        ClassWithUsers.USER_ID +
                    " FROM " +
                        ClassWithUsers.TABLENAME +
                    " WHERE " +
                        ClassWithUsers.CLASS_ID + "=" + '"' + classId.ToString() + "\";" )
                .Tables[0];
            int iMax = dataTable.Rows.Count;
            for ( int i = 0; i < iMax; i++ ) {
                cwu.UserList.Add( GetUserDetails(( int )dataTable.Rows[i][0] ));
            }
            return cwu;
        }
        
        public int SetClass( Class @class ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Class.TABLENAME + " (" +
                        Class.CLASS_ID + ", " +
                        Class.COURSE_ID + ", " +
                        Class.CLASS_NAME + ", " +
                        Class.CLASS_BEGIN + ", " +
                        Class.CLASS_END +
                    " ) VALUES (" +
                        @class.ClassId + ", " +
                        @class.Course.CourseId + ", " +
                        '"' + @class.ClassName + '"' + ", " +
                        '"' + @class.ClassBegin + '"' + ", " +
                        '"' + @class.ClassEnd + '"' +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Class.TABLENAME +
                            " SET " +
                                Class.COURSE_ID + "=" + @class.Course.CourseId + ", " +
                                Class.CLASS_NAME + "=" + '"' + @class.ClassName + '"' + ", " +
                                Class.CLASS_BEGIN + "=" + '"' + @class.ClassBegin + '"' + ", " +
                                Class.CLASS_END + "=" + '"' + @class.ClassEnd + '"' +
                            " WHERE " +
                                Class.CLASS_ID + "=" + @class.ClassId + ";" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }

        /// <summary>
        /// (k) Über einen User (genauer seiner Id) ein Klassen-Objekt ermitteln
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public Class GetClass( User user ) {
            Class @class = new Class();
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            Class.CLASS_ID +
                        " FROM " +
                            "class_user" +
                        " WHERE " +
                            User.USER_ID + "=" + user.UserId.ToString() )
                    .Tables[0];
                return GetClass( ( int )dataTable.Rows[0][0] );
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }

        // exam, examquestions, examuser
        public Exam GetExam( int examId ) {

            Exam exam = new Exam();

            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            Exam.EXAM_ID + ", " +
                            Exam.EXAM_CLASS_ID + ", " +
                            Exam.EXAM_MODULE_ID + ", " +
                            Exam.EXAM_NAME + ", " +
                            Exam.EXAM_CREATE_DATE_TIME + ", " +
                            Exam.EXAM_WRITE_DATE_TIME + ", " +
                            Exam.EXAM_DURATION + ", " +
                            Exam.EXAM_AUTHOR_ID + " " +
                        " FROM " +
                            Exam.TABLENAME +
                        " WHERE " +
                            Exam.EXAM_ID + "=" + examId )
                    .Tables[0];

                exam.ExamId = ( int )dataTable.Rows[0][0];
                exam.ExamClass = GetClass( ( int )dataTable.Rows[0][1] );
                exam.ExamModule = GetModule( ( int )dataTable.Rows[0][2] );
                exam.ExamName = ( string )dataTable.Rows[0][3];
                exam.ExamCreateDate = ( DateTime )dataTable.Rows[0][4];
                exam.ExamWriteDate = ( DateTime )dataTable.Rows[0][5];
                exam.ExamDuration = ( int )dataTable.Rows[0][6];
                exam.ExamAuthor = GetUser( ( int )dataTable.Rows[0][7] );

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }

            return exam;
        }
        public int SetExam( Exam exam ) {
            try {

                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + Exam.TABLENAME + " (" +
                        Exam.EXAM_ID + ", " +
                        Exam.EXAM_CLASS_ID + ", " +
                        Exam.EXAM_MODULE_ID + ", " +
                        Exam.EXAM_NAME + ", " +
                        Exam.EXAM_CREATE_DATE_TIME + ", " +
                        Exam.EXAM_WRITE_DATE_TIME + ", " +
                    //Exam.EXAM_TIME + ", " +
                        Exam.EXAM_DURATION + ", " +
                        Exam.EXAM_AUTHOR_ID +
                    " ) VALUES (" +
                        exam.ExamId + ", " +
                        exam.ExamClass.ClassId + ", " +
                        exam.ExamModule.ModuleId + ", " +
                        '"' + exam.ExamName + '"' + ", " +
                        '"' + exam.ExamCreateDate + '"' + ", " +
                        '"' + exam.ExamWriteDate + '"' + ", " +
                    //'"' + exam.ExamTime + '"' + ", " +
                        exam.ExamDuration + ", " +
                        exam.ExamAuthor.UserId +
                    " );" );
                if ( rowsAffected <= 0 )
                    rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + Exam.TABLENAME +
                            " SET " +
                                Exam.EXAM_ID + "=" + exam.ExamId + ", " +
                                Exam.EXAM_CLASS_ID + "=" + exam.ExamClass.ClassId + ", " +
                                Exam.EXAM_MODULE_ID + "=" + exam.ExamModule.ModuleId + ", " +
                                Exam.EXAM_NAME + "=" + '"' + exam.ExamName + '"' + ", " +
                                Exam.EXAM_CREATE_DATE_TIME + "=" + '"' + exam.ExamCreateDate + '"' + ", " +
                                Exam.EXAM_WRITE_DATE_TIME + "=" + '"' + exam.ExamWriteDate + '"' + ", " +
                        //Exam.EXAM_TIME + "=" + '"' + exam.ExamTime + '"' + ", " +
                                Exam.EXAM_DURATION + "=" + exam.ExamDuration + ", " +
                                Exam.EXAM_AUTHOR_ID + "=" + exam.ExamAuthor.UserId +
                            " WHERE " +
                                Exam.EXAM_ID + "=" + exam.ExamId + " AND " +
                                Exam.EXAM_CLASS_ID + "=" + exam.ExamClass.ClassId + " AND " +
                                Exam.EXAM_AUTHOR_ID + "=" + exam.ExamAuthor.UserId +
                            " ;" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }

        /// <summary>
        /// (k) Holt alle Klasuren für eine Klasse aus der Datenbank
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public List<Exam> GetExamList( int ClassId ) {
            List<Exam> examList = new List<Exam>();
            // Die examIds ermitteln, die auf die ClassIds passen
            DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        Exam.EXAM_ID +
                    " FROM " +
                        Exam.TABLENAME +
                    " WHERE " +
                        Exam.EXAM_CLASS_ID + "=" + ClassId )
                .Tables[0];
            int iMax = dataTable.Rows.Count;
            // Die examList mit Exams füllen, die aus der Datenbank mit der ExamId geholt werden...
            for ( int i = 0; i < iMax; i++ ) {
                examList.Add( GetExam( ( int )dataTable.Rows[i][0] ) );
            }
            return examList;
        }
        /// <summary>
        /// (k) Holt alle geschriebenen Klasuren für einen User aus der Datenbank
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public List<ExamUser> GetExamUserListByUserId( int userId ) {
            List<ExamUser> examUserList = new List<ExamUser>();
            // Die examIds ermitteln, die auf die userId passen
            DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        ExamUser.EXAM_ID +
                    " FROM " +
                        ExamUser.TABLENAME +
                    " WHERE " +
                        ExamUser.USER_ID + "=" + userId )
                .Tables[0];
            int iMax = dataTable.Rows.Count;
            // Die examList mit Exams füllen, die aus der Datenbank mit der ExamId geholt werden...
            App.DatabaseConnection.Verbose = false;
            for ( int i = 0; i < iMax; i++ ) {
                ExamUser eu = GetExamUser( userId, ( int )dataTable.Rows[i][0] );
                if (eu!=null)
                    examUserList.Add( eu );
            }
            App.DatabaseConnection.Verbose = true;
            return examUserList;
        }
        /// <summary>
        /// (k) Holt alle noch zu schreibenden Klasuren für einen User aus der Datenbank
        /// </summary>
        /// <param name="ClassId"></param>
        /// <returns></returns>
        public List<ExamWithQuestions> GetExamWithQuestionsListByUserId( int userId ) {
            List<ExamWithQuestions> examWithQuestionsList = new List<ExamWithQuestions>();
            // Die examIds ermitteln, die auf die userId passen
            DataTable dataTable = GetDataSetByUsingSELECTStatement(
                    "SELECT " +
                        ExamUser.EXAM_ID +
                    " FROM " +
                        ExamUser.TABLENAME +
                    " WHERE " +
                        ExamUser.USER_ID + "=" + userId )
                .Tables[0];
            int iMax = dataTable.Rows.Count;
            // Die examList mit Exams füllen, die aus der Datenbank mit der ExamId geholt werden...
            App.DatabaseConnection.Verbose = false;
            for ( int i = 0; i < iMax; i++ ) {
                ExamUser eu = GetExamUser( userId, ( int )dataTable.Rows[i][0] );
                if ( eu == null ) // wenn null, dann wurde klausur noch nicht geschrieben
                    examWithQuestionsList.Add( GetExamWithQuestions( ( int )dataTable.Rows[i][0] ) );
            }
            App.DatabaseConnection.Verbose = true;
            return examWithQuestionsList;
        }
        /// <summary>
        /// (k) Wie GetExamList( int ClassId ); jedoch wird versucht, das in der liste
        /// zu speichern, so weit wie möglich zu spezialisieren...
        /// --> ExamWithQuestions (Vorlage) --> ExamUser (ausgefüllte Klausur)
        /// </summary>
        /// <param name="examList"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<Exam> TryGetSpezializedExamList( List<Exam> examList, int userId ) {
            List<Exam> newExamList = new List<Exam>();
            foreach ( Exam ex in examList ) {
                App.DatabaseConnection.Verbose = false;
                ExamUser eu = GetExamUser( userId, ex.ExamId );
                if (eu!=null)
                    newExamList.Add( eu );
                else
                    newExamList.Add( GetExamWithQuestions( ex.ExamId ) );
            }
            App.DatabaseConnection.Verbose = true;
            return newExamList;
        }



        public ExamWithQuestions GetExamWithQuestions( Exam exam ) {

            ExamWithQuestions examQuestions = new ExamWithQuestions( exam );

            try {

                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            ExamWithQuestions.QUESTION_ID + ", " +
                            ExamWithQuestions.POINTS +
                        " FROM " +
                            ExamWithQuestions.TABLENAME +
                        " WHERE " +
                            ExamWithQuestions.EXAM_ID + "=" + exam.ExamId + ";" )
                    .Tables[0];

                int iMax = dataTable.Rows.Count;
                List<Question> tempList = new List<Question>();
                for ( int i = 0; i < iMax; i++ ) {
                    Question q = GetQuestion( ( int )dataTable.Rows[i][0] );
                    tempList.Add( q );
                    examQuestions.PointsList.Add( ( int )dataTable.Rows[i][1] );
                }

                examQuestions.AnswerList = new List<Answer>();
                foreach ( Question question in tempList ) {
                    switch ( question.QuestionTypeObject ) {
                        case ( "multiple" ):
                            AnswerTypeMultiple answerTypeMultiple = new AnswerTypeMultiple( examQuestions.ExamId, question );
                            examQuestions.AnswerList.Add( answerTypeMultiple );
                            break;
                        case ( "single_multiple" ):
                            AnswerTypeSingleMultiple answerTypeSingleMultiple = new AnswerTypeSingleMultiple( examQuestions.ExamId, question );
                            examQuestions.AnswerList.Add( answerTypeSingleMultiple );
                            break;
                        case ( "text" ):
                            AnswerTypeText answerTypeText = new AnswerTypeText( examQuestions.ExamId, question );
                            examQuestions.AnswerList.Add( answerTypeText );
                            break;
                        default:
                            Answer answer = new Answer( examQuestions.ExamId, question );
                            examQuestions.AnswerList.Add( answer );
                            break;
                    }
                }

                return examQuestions;

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public ExamWithQuestions GetExamWithQuestions( int examId ) {
            return GetExamWithQuestions( GetExam( examId ) );
        }
        public ExamUser GetExamUser( User user, ExamWithQuestions examQuestions ) {
            ExamUser examUser = new ExamUser( examQuestions );
            try {
                DataTable dataTable = GetDataSetByUsingSELECTStatement(
                        "SELECT " +
                            ExamUser.EXAM_ID + ", " +
                            ExamUser.USER_ID + ", " +
                            ExamUser.START_DATETIME + ", " +
                            ExamUser.END_DATETIME +
                        " FROM " +
                            ExamUser.TABLENAME +
                        " WHERE " +
                            ExamUser.EXAM_ID + "=" + examQuestions.ExamId + " AND " +
                            ExamUser.USER_ID + "=" + user.UserId )
                    .Tables[0];

                if ( user is UserDetails )
                    examUser.UserDetails = ( UserDetails )user;
                else
                    examUser.UserDetails = GetUserDetails( ( int )dataTable.Rows[0][1] );
                examUser.StartDateTime = ( DateTime )dataTable.Rows[0][2];
                examUser.EndDateTime = ( DateTime )dataTable.Rows[0][3];
                return examUser;

            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( "Fehler bei der Datenbankabfrage", ex );
                return null;
            }
        }
        public ExamUser GetExamUser( int userId, int examId ) {
            UserDetails ud = GetUserDetails( userId );
            ExamWithQuestions eq = GetExamWithQuestions( examId );
            return GetExamUser( ud, eq );
        }
        public int SetExamUser( ExamUser examUser ) {
            try {
                int rowsAffected = -1;
                rowsAffected = SetEntriesByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + ExamUser.TABLENAME + " (" +
                        ExamUser.EXAM_ID + ", " +
                        ExamUser.USER_ID + ", " +
                        ExamUser.START_DATETIME + ", " +
                        ExamUser.END_DATETIME +
                    " ) VALUES (" +
                        examUser.ExamId + ", " +
                        examUser.UserDetails.UserId + ", " +
                        '"' + Tools.ToMySQLDate( examUser.StartDateTime ) + '"' + ", " +
                        '"' + Tools.ToMySQLDate( examUser.EndDateTime ) + '"' +
                    " );" );
                return rowsAffected;
            } catch ( Exception ex ) {
                if ( Verbose )
                    Display( ex );
                return -1;
            }
        }


        /// <summary>
        /// (k) Diese Methode erstellt eine Klasur im ComViewExam-Container. Darin enthalten sein werden auch
        /// die Klasurfrage (ExamQuestions) und darin auch die (Exam)Klausurinformationen. 
        /// </summary>
        /// <param name="examId">Exam.ExamId</param>
        /// <param name="userId">darf auch -1 sein, dann bleibt ExamUser==null</param>
        /// <returns></returns>
        public ViewExam GetComViewExam( int examId ) {
            return new ViewExam(GetExamWithQuestions( examId ));
        }
        /// <summary>
        /// (k) Diese Methode versucht zusätzlich zu ermitteln, ob eine Klausur bereits
        /// ausgefüllt in der Datenbank liegt und ruft diese Daten ab. (ExamQuestions->ExamUser)
        /// </summary>
        /// <param name="examId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public ViewExam GetComViewExam( int examId, int userId ) {
            ViewExam comViewExam = GetComViewExam( examId );
            if ( userId != -1 ) {
                App.DatabaseConnection.Verbose = false;
                ExamUser eu = GetExamUser( userId, examId );
                if ( eu != null ) {
                    comViewExam.Exam = eu;
                    for ( int i = 0; i < eu.AnswerList.Count; i++ ) { // jetzt fehlen noch die gegebenen antworten;...
                        eu.AnswerList[i] = GetAnswer( eu.ExamId, eu.AnswerList[i].AnswerQuestion, eu.UserDetails.UserId );
                    }
                }
                App.DatabaseConnection.Verbose = true;
            }
            return comViewExam;
        }

        public void SetComViewExam( ViewExam comViewExam ) {
            if ( comViewExam.Exam is ExamUser ) {
                // hier wird die ausgefüllte klausur in die datenbank geschrieben
                /* -exam_user füllen (examid, userid, start,end,(rating))
                 * -spezielle answer-tabelle füllen
                 *      -answer füllem (examid, questionid, userid, answernumber, answerrating)
                 * 
                 */

                SetExamUser( ( ExamUser )comViewExam.Exam );
                ExamUser eu = ( ExamUser )comViewExam.Exam;
                foreach ( Answer answer in eu.AnswerList ) {
                    SetAnswer( answer );
                }
            } else {
                // hier müßte dann die neue Klausurvorlage in die Datenbank geschrieben werden
            }
        }



        // Bei Eintragung neuer Objekte wird die Ermittlung der neuen Id wichtig sein...
        public int GetNewUserId() {
            return GetMax( User.USER_ID, User.TABLENAME );
        }

        public int GetNewAnswerTypeMultipleId() {
            return GetMax( AnswerTypeMultiple.ANSWER_NUMBER, AnswerTypeMultiple.TABLENAME );
        }
        public int GetNewAnswerTypeSingleMultipleId() {
            return GetMax( AnswerTypeSingleMultiple.ANSWER_NUMBER, AnswerTypeSingleMultiple.TABLENAME );
        }
        public int GetNewAnswerTypeTextId() {
            return GetMax( AnswerTypeText.ANSWER_NUMBER, AnswerTypeText.TABLENAME );
        }
        public int GetNewQuestionId() {
            return GetMax( Question.QUESTION_ID, QuestionTypeMultiple.TABLENAME );
        }
        public int GetNewQuestionTypeMultipleId() {
            return GetMax( QuestionTypeMultiple.QUESTION_NUMBER, QuestionTypeMultiple.TABLENAME );
        }
        public int GetNewQuestionTypeSingleMultipleId() {
            return GetMax( QuestionTypeSingleMultiple.QUESTION_NUMBER, QuestionTypeSingleMultiple.TABLENAME );
        }
        public int GetNewQuestionTypeTextId() {
            return GetMax( QuestionTypeText.QUESTION_NUMBER, QuestionTypeText.TABLENAME );
        }


        /*QuestionModule*/
        /*public QuestionModule GetQuestionModuleObjectFromDatabase(int questionId)
        {
            QuestionModule questionModule = new QuestionModule();
            try
            {

                return questionModule;
            }
            catch (Exception ex)
            {
                if (Verbose)
                    Display("Fehler bei der Datenbankabfrage", ex);
                return null;
            }
        }*/
        /*public int SetExamQuestionObjectIntoDatabase(ExamQuestion examQuestion)
        {
            try
            {
                int rowsAffected = -1;
                rowsAffected = SetEntriesInDatabaseByUsingINSERTOrUPDATEStatement(
                    " INSERT INTO " + ExamQuestion.NAME + " ( " +
                        ExamQuestion.EXAM_ID + ", " +
                        ExamQuestion.QUESTION_ID + ", " +
                        ExamQuestion.POINTS +
                    " ) VALUES ( " +
                        examQuestion.Exam.ExamId + ", " +
                        examQuestion.Question.QuestionId + ", " +
                        examQuestion.Points +
                    " );");
                if (rowsAffected <= 0)
                    rowsAffected = SetEntriesInDatabaseByUsingINSERTOrUPDATEStatement(
                            " UPDATE " + ExamQuestion.NAME +
                            " SET " +
                                ExamQuestion.POINTS + "=" + examQuestion.Points + " " +
                            " WHERE " +
                                ExamQuestion.EXAM_ID + "=" + examQuestion.Exam.ExamId + " AND " +
                                ExamQuestion.QUESTION_ID + "=" + examQuestion.Question.QuestionId + ";");
                return rowsAffected;
            }
            catch (Exception ex)
            {
                if (Verbose)
                    Display(ex);
                return -1;
            }
        }*/
        /*public CourseModule GetCourseModuleObjectFromDatabase(int courseId)
        {
            try
            {
                return null;
            }
            catch (Exception ex)
            {
                if (Verbose)
                    Display("Fehler bei der Datenbankabfrage", ex);
                return null;
            }
        }*/
        /*ClassUser*/
        /*CourseModule*/



        public static void Display( object messageObject ) {
            Display( messageObject, null );
        }
        public static void Display( object messageObject, Exception ex ) {
            string messageString = "Datenbank: " + messageObject.ToString();
            App.Display( messageString, ex );
        }
    }
}